<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Chapter&nbsp;8.&nbsp;jBPM 的数据库</title>
    <link rel="stylesheet" href="css/html.css" type="text/css">
    <meta name="generator" content="DocBook XSL Stylesheets V1.65.1">
    <link rel="home" href="index.html" title="JBoss jBPM 3.1">
    <link rel="up" href="index.html" title="JBoss jBPM 3.1">
    <link rel="previous" href="persistence.html" title="Chapter&nbsp;7.&nbsp;Persistence">
    <link rel="next" href="processmodelling.html" title="Chapter&nbsp;9.&nbsp;Process Modelling">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
    <table width="100%" summary="Navigation header">
        <tr>
            <th colspan="3" align="center">Chapter&nbsp;8.&nbsp;jBPM 的数据库</th>
        </tr>
        <tr>
            <td width="20%" align="left"><a accesskey="p" href="persistence.html">Prev</a>&nbsp;</td>
            <th width="60%" align="center">&nbsp;</th>
            <td width="20%" align="right">&nbsp;<a accesskey="n" href="processmodelling.html">Next</a></td>
        </tr>
    </table>
    <hr>
</div>
<div class="chapter" lang="cn">
<div class="titlepage">
    <div>
        <div><h2 class="title"><a name="thejbpmdatabase"></a>Chapter&nbsp;8.&nbsp;jBPM 的数据库</h2></div>
    </div>
    <div></div>
</div>
<div class="section" lang="cn">
<div class="titlepage">
    <div>
        <div><h2 class="title" style="clear: both"><a name="d0e2272"></a>8.1.&nbsp;切换数据库后端</h2>
        </div>
    </div>
    <div></div>
</div>
<p>Switching the JBoss jBPM database backend is reasonably
    straightforward. We will step through this process using PostgreSQL as an
    example. The process is identical for all other supported databases. For a
    number of these supported databases, a number of JDBC drivers, Hibernate
    configuration files and Ant build files to generate the database creation
    scripts are present in the jBPM distribution in the DB subproject. If you
    cannot find these files for the database you wish to use, you should first
    make sure if Hibernate supports your database. If this is the case you can
    have a look at files for one of the databases present in the DB project
    and mimic this using your own database.</p>

<p>For this document, we will use the JBoss jBPM Starter's Kit
    distribution. We will assume that this starter's kit was extracted to a
    location on your machine named ${JBPM_SDK_HOME}. You will find the DB
    subproject of jBPM in the ${JBPM_SDK_HOME}/jbpm-db.</p>

<p>After installing the database, you will have to run the database
    creation scripts. These will create the jBPM tables in the database. To
    make the default webapp running with this new database, we will have to
    update some configuration files of the server included in the Starter's
    Kit. For these configuration changes, we will not go into too much detail.
    If you want to know more about the different configuration settings in the
    server, we advise you to have a look at the JBoss documentation.</p>

<div class="section" lang="cn">
    <div class="titlepage">
        <div>
            <div><h3 class="title"><a name="d0e2281"></a>8.1.1.&nbsp;Installing the PostgreSQL Database Manager</h3>
            </div>
        </div>
        <div></div>
    </div>
    <p>To install PostgreSQL or any other database you may be using, we
        refer to the installation manual of these products. For Windows
        PostgreSQL installation is pretty straightforward. The installer creates
        a dedicated Windows user and allows to define the database
        administrator. PostgreSQL comes with an administration tool called
        pgAdmin III that we will use to create the jBPM database. A screenshot
        of this tool right after creating the JbpmDB database with it is shown
        in the figure below.</p>

    <div class="figure"><a name="d0e2286"></a>

        <div class="mediaobject"><img src="images/create_database.jpg"
                                      alt="The PostgreSQL pgAdmin III tool after creating the JbpmDB database"></div>
        <p class="title"><b>Figure&nbsp;8.1.&nbsp;The PostgreSQL pgAdmin III tool after creating the JbpmDB
            database</b></p></div>
    <p>After the installation of the database, we can use a database
        viewer tool like DBVisualizer to look at the contents of the database.
        Before you can define a database connection with DBVisualizer, you might
        have to add the PostgreSQL JDBC driver to the driver manager. Select
        'Tools-&gt;Driver Manager...' to open the driver manager window. Look at
        the figure below for an example of how to add the PostgreSQL JDBC
        driver.</p>

    <div class="figure"><a name="d0e2294"></a>

        <div class="mediaobject"><img src="images/install_driver.jpg"
                                      alt="Adding the JDBC driver to the driver manager"></div>
        <p class="title"><b>Figure&nbsp;8.2.&nbsp;Adding the JDBC driver to the driver manager</b></p></div>
    <p>Now everything is set to define a database connection in
        DBVisualizer to our newly created database. We will use this tool
        further in this document to make sure the creation scripts and process
        deployment are working as expected. For an example of creating the
        connection in DBVisualizer we refer to the following figure. As you can
        see, there are no tables present yet in this database. We will create
        them in the following section.</p>

    <div class="figure"><a name="d0e2302"></a>

        <div class="mediaobject"><img src="images/create_connection.jpg"
                                      alt="Create the connection to the jBPM database"></div>
        <p class="title"><b>Figure&nbsp;8.3.&nbsp;Create the connection to the jBPM database</b></p></div>
    <p>Another thing worth mentioning is the Database URL above :
        'jdbc:postgresql://localhost:5432/JbpmDB'. If you created the JbpmDB
        database with another name, or if PostgreSQL is not running on the
        localhost machine or on another port, you'll have to adapt your Database
        URL accordingly.</p></div>
<div class="section" lang="cn">
    <div class="titlepage">
        <div>
            <div><h3 class="title"><a name="d0e2310"></a>8.1.2.&nbsp;Creating the JBoss jBPM Database</h3></div>
        </div>
        <div></div>
    </div>
    <p>As already mentioned you will find the database scripts for a lot
        of the supported databases in the DB subproject. The database scripts
        for PostgreSQL are found in the folder
        '${JBPM_SDK_HOME}/jbpm-db/build/postgresql/scripts. The creation script
        is called 'postgresql.create.sql'. Using DBVisualizer, you can load this
        script by switching to the 'SQL Commander' tab and then selecting
        'File-&gt;Load...'. In the following dialog, navigate to the creation
        script file. The result of doing so is shown in the figure below.</p>

    <div class="figure"><a name="d0e2315"></a>

        <div class="mediaobject"><img src="images/load_script.jpg" alt="Load the database creation script"></div>
        <p class="title"><b>Figure&nbsp;8.4.&nbsp;Load the database creation script</b></p></div>
    <p>To execution this script with DBVisualizer, you select
        'Database-&gt;Execute'. After this step all JBoss jBPM tables are
        created. The situation is illustrated in the figure below.</p>

    <div class="figure"><a name="d0e2323"></a>

        <div class="mediaobject"><img src="images/execute_script.jpg" alt="Running the database creation script"></div>
        <p class="title"><b>Figure&nbsp;8.5.&nbsp;Running the database creation script</b></p></div>
    <p>After these steps, there is not yet any data present in the
        tables. For the jBPM webapp to work, you should at least create some
        records in the jbpm_id_user table. In order to have exactly the same
        entries in this table as the default distribution of the starter's kit
        running on HSQLDB, we suggest to run the script below.</p><pre class="programlisting">insert into JBPM_ID_USER (ID_, CLASS_, NAME_, EMAIL_, PASSWORD_)
       values ('1', 'U', 'cookie monster', 'cookie.monster@sesamestreet.tv', 'crunchcrunch');
insert into JBPM_ID_USER (ID_,CLASS_, NAME_, EMAIL_, PASSWORD_) 
       values ('2', 'U', 'ernie', 'ernie@sesamestreet.tv', 'canthereyoubert,theresabananainmyear');
insert into JBPM_ID_USER (ID_,CLASS_, NAME_, EMAIL_, PASSWORD_) 
       values ('3', 'U', 'bert', 'bert@sesamestreet.tv', 'ernie,theresabananainyourear');
insert into JBPM_ID_USER (ID_,CLASS_, NAME_, EMAIL_, PASSWORD_) 
       values ('4', 'U', 'grover', 'grover@sesamestreet.tv', 'mayday mayday');</pre>
</div>
<div class="section" lang="cn">
    <div class="titlepage">
        <div>
            <div><h3 class="title"><a name="d0e2333"></a>8.1.3.&nbsp;Update the JBoss jBPM Server Configuration</h3>
            </div>
        </div>
        <div></div>
    </div>
    <p>Before we can really use our newly created database with the JBoss
        jBPM default webapp we will have to do some updates to the JBoss jBPM
        configuration. The location of the server is
        '${JBPM_SDK_HOME}/jbpm-server'. The first thing we will be doing to
        update this configuration is create a new datasource that points to our
        JbpmDB database. In a second step, we will make sure that the default
        webapp is talking to this datasource and not to the HSQLDB datasource
        anymore.</p><pre class="programlisting">&lt;?xml version="1.0" encoding="UTF-8"?&gt;

&lt;datasources&gt;
  &lt;local-tx-datasource&gt;
    &lt;jndi-name&gt;JbpmDS&lt;/jndi-name&gt;
    &lt;connection-url&gt;jdbc:postgresql://localhost:5432/JbpmDB&lt;/connection-url&gt;
    &lt;driver-class&gt;org.postgresql.Driver&lt;/driver-class&gt;
    &lt;user-name&gt;user&lt;/user-name&gt;
    &lt;password&gt;password&lt;/password&gt;
    &lt;metadata&gt;
      &lt;type-mapping&gt;PostgreSQL 8.1&lt;/type-mapping&gt;
    &lt;/metadata&gt;
  &lt;/local-tx-datasource&gt;
&lt;/datasources&gt;</pre>
    <p>In order to create a new datasource, you should create a file
        named e.g. jbpm-ds.xml with the contents shown in the program listing
        above. Of course it is possible that you have to change some of the
        values in this file to accommodate for your particular situation. You
        then simply save this file in the
        ${JBPM_SDK_HOME}/jbpm-server/server/jbpm/deploy folder. Congratulations,
        you just created a new DataSource for your JBoss jBPM server. Well,
        almost... To make things really work you will have to copy the correct
        JDBC driver to the ${JBPM_SDK_HOME}/jbpm-server/server/jbpm/lib folder.
        We already used this JDBC driver above when we were installing it in
        DBVisualizer to be able to browse our newly created database. The file
        is named 'postgresql-8.1-*.jdbc3.jar' and it can be found in the jdbc
        subfolder of your PostgreSQL installation folder.</p>

    <p>If you are not using PostgreSQL, you may wonder where you will
        find the parameters to accomplish this step. For a big amount of
        databases supported by the JBoss Application Server, you should download
        a JBoss AS distribution and have a look in the 'docs/examples/jca'
        folder.</p>

    <p>Making the default webapp talk to the correct datasource is again
        not very difficult. The first step in doing this is simply locate the
        'jboss-service.xml' file in the folder
        '${JBPM_SDK_HOME}/jbpm-server/server/jbpm/deploy/jbpm.sar/META-INF'.
        Change the contents of this file with the contents of the listing below.
        An attentive reader will notice that the only difference is an exchange
        of the token 'DefaultDS' by 'JbpmDS'.</p><pre class="programlisting">&lt;?xml version="1.0" encoding="UTF-8"?&gt;

&lt;server&gt;
  &lt;mbean code="org.jbpm.db.jmx.JbpmService" 
         name="jboss.jbpm:name=DefaultJbpm,service=JbpmService"
         description="Default jBPM Service"&gt;
    &lt;attribute name="JndiName"&gt;java:/jbpm/JbpmConfiguration&lt;/attribute&gt;
    &lt;depends&gt;jboss.jca:service=DataSourceBinding,name=JbpmDS&lt;/depends&gt;
  &lt;/mbean&gt;
&lt;/server&gt;</pre>
    <p>The last thing we have to do to make everything run is a
        manipulation of the 'jbpm.sar.cfg.jar' file in the
        '${JBPM_SDK_HOME}/jbpm-server/server/jbpm/deploy/jbpm.sar' folder. You
        have to extract this file somewhere and open the file named
        'hibernate.cfg.xml'. Then replace the section containing the jdbc
        connection properties. This section should look like shown in the
        listing below. There are two changes in this file : the
        hibernate.connection.datasource property should point to the JbpmDS
        datasource we created as the first step in this section and the
        hibernate.dialect property should match the PostgreSQL dialect.</p><pre class="programlisting">&lt;?xml version='1.0' encoding='utf-8'?&gt;

&lt;!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;

&lt;hibernate-configuration&gt;
  &lt;session-factory&gt;

    &lt;!-- jdbc connection properties --&gt;
    &lt;property name="hibernate.dialect"&gt;org.hibernate.dialect.PostgreSQLDialect&lt;/property&gt;
    &lt;property name="hibernate.connection.datasource"&gt;java:/JbpmDS&lt;/property&gt;
        
    &lt;!-- other hibernate properties 
    &lt;property name="hibernate.show_sql"&gt;true&lt;/property&gt;
    &lt;property name="hibernate.format_sql"&gt;true&lt;/property&gt;
    --&gt;
    
    &lt;!-- ############################################ --&gt;
    &lt;!-- # mapping files with external dependencies # --&gt;
    &lt;!-- ############################################ --&gt;

    ...

  &lt;/session-factory&gt;
&lt;/hibernate-configuration&gt;</pre>
    <p>Now we are ready to fire up the server, and look if the webapp
        works. You will not be able to start any processes yet, as there are no
        processes deployed yet. To do this we refer to the document on process
        definition deployment.</p></div>
</div>
<div class="section" lang="cn">
    <div class="titlepage">
        <div>
            <div><h2 class="title" style="clear: both"><a name="databaseupgrades"></a>8.2.&nbsp;数据库升级</h2>
            </div>
        </div>
        <div></div>
    </div>
    <p>In the jbpm.db subproject, you can find:
    </p>

    <div class="itemizedlist">
        <ul type="disc">
            <li>An SQL script to create the jBPM 3.0.2 schema (for Hypersonic)</li>
            <li>An SQL script to create the jBPM 3.1 schema (for Hypersonic)</li>
            <li>An SQL script to upgrade from a jBPM 3.0.2 schema to a jBPM 3.1 schema (for Hypersonic)</li>
            <li>The ant scripts to create the schema update</li>
        </ul>
    </div>
    <p>The schema SQL scripts can be found in directory <tt class="literal">hsqldb/upgrade.scripts</tt>.
    </p>

    <p>To run the schema update tool for your database, follow these guidelines:
    </p>

    <div class="itemizedlist">
        <ul type="disc">
            <li>Prerequisite: Make sure you installed the jbpm.db project right besides the jbpm
                project. In the starters-kit, this is automatically the case. If jbpm is installed in a
                different location, update the jbpm.3.location dir in build.properties accordingly.
            </li>
            <li>Prerequisite: You should have the proper JDBC driver jar for your database.
            </li>
            <li>Update the properties in the build.properties in the root of the jbpm.db project:
                <div class="itemizedlist">
                    <ul type="circle">
                        <li><span class="bold"><b>upgrade.hibernate.properties</b></span>: a properties file
                            that contains the connection properties to your database in hibernate style.
                        </li>
                        <li><span class="bold"><b>upgrade.libdir</b></span>: the directory containing
                            the jdbc driver jars for your database.
                        </li>
                        <li><span class="bold"><b>upgrade.old.schema.script</b></span>: the schema generation
                            script to create the old database schema. (if it already exists, you don't need this
                            property.)
                        </li>
                    </ul>
                </div>
            </li>
            <li>For creating the old schema and then calculating the differences, run the ant
                script <span class="bold"><b><tt class="literal">'ant upgrade.db.script'</tt></b></span></li>
            <li>For only calculating the update script without first loading the old
                db schema, run the ant
                script <span class="bold"><b><tt class="literal">'ant upgrade.hibernate.schema.update'</tt>
            </b></span></li>
            <li>After successfull completion, you'll find the upgrade script in
                <tt class="literal">build/database.upgrade.sql</tt></li>
        </ul>
    </div>
    <p>For upgrading from jBPM 3.0.2 to jBPM 3.1, the generated upgrade SQL script (for HSQLDB) is
        illustrated in the listing below:
    </p><pre class="programlisting"># New JBPM_MESSAGE table
create table JBPM_MESSAGE (
  ID_ bigint generated by default as identity (start with 1), 
  CLASS_ char(1) not null, 
  DESTINATION_ varchar(255), 
  EXCEPTION_ varchar(255), 
  ISSUSPENDED_ bit, 
  TOKEN_ bigint, 
  TEXT_ varchar(255), 
  ACTION_ bigint, 
  NODE_ bigint, 
  TRANSITIONNAME_ varchar(255), 
  TASKINSTANCE_ bigint, 
  primary key (ID_)
);

# Added columns
alter table JBPM_ACTION add column ACTIONEXPRESSION_ varchar(255);
alter table JBPM_ACTION add column ISASYNC_ bit;
alter table JBPM_COMMENT add column VERSION_ integer;
alter table JBPM_ID_GROUP add column PARENT_ bigint;
alter table JBPM_NODE add column ISASYNC_ bit;
alter table JBPM_NODE add column DECISIONEXPRESSION_ varchar(255);
alter table JBPM_NODE add column ENDTASKS_ bit;
alter table JBPM_PROCESSINSTANCE add column VERSION_ integer;
alter table JBPM_PROCESSINSTANCE add column ISSUSPENDED_ bit;
alter table JBPM_RUNTIMEACTION add column VERSION_ integer;
alter table JBPM_SWIMLANE add column ACTORIDEXPRESSION_ varchar(255);
alter table JBPM_SWIMLANE add column POOLEDACTORSEXPRESSION_ varchar(255);
alter table JBPM_TASK add column ISSIGNALLING_ bit;
alter table JBPM_TASK add column ACTORIDEXPRESSION_ varchar(255);
alter table JBPM_TASK add column POOLEDACTORSEXPRESSION_ varchar(255);
alter table JBPM_TASKINSTANCE add column CLASS_ char(1);
alter table JBPM_TASKINSTANCE add column ISSUSPENDED_ bit;
alter table JBPM_TASKINSTANCE add column ISOPEN_ bit;
alter table JBPM_TIMER add column ISSUSPENDED_ bit;
alter table JBPM_TOKEN add column VERSION_ integer;
alter table JBPM_TOKEN add column ISSUSPENDED_ bit;
alter table JBPM_TOKEN add column SUBPROCESSINSTANCE_ bigint;
alter table JBPM_VARIABLEINSTANCE add column TASKINSTANCE_ bigint;

# Added constraints
alter table JBPM_ID_GROUP add constraint FK_ID_GRP_PARENT foreign key (PARENT_) references JBPM_ID_GROUP;
alter table JBPM_MESSAGE add constraint FK_MSG_TOKEN foreign key (TOKEN_) references JBPM_TOKEN;
alter table JBPM_MESSAGE add constraint FK_CMD_NODE foreign key (NODE_) references JBPM_NODE;
alter table JBPM_MESSAGE add constraint FK_CMD_ACTION foreign key (ACTION_) references JBPM_ACTION;
alter table JBPM_MESSAGE add constraint FK_CMD_TASKINST foreign key (TASKINSTANCE_) references JBPM_TASKINSTANCE;
alter table JBPM_TOKEN add constraint FK_TOKEN_SUBPI foreign key (SUBPROCESSINSTANCE_) references JBPM_PROCESSINSTANCE;
alter table JBPM_VARIABLEINSTANCE add constraint FK_VAR_TSKINST foreign key (TASKINSTANCE_) references JBPM_TASKINSTANCE;</pre>
</div>
<div class="section" lang="cn">
    <div class="titlepage">
        <div>
            <div><h2 class="title" style="clear: both"><a name="d0e2414"></a>8.3.&nbsp;启动JBoss上的 hsqldb 管理器
            </h2></div>
        </div>
        <div></div>
    </div>
    <p>Not really crucial for jBPM, but in some situations during development, it can be
        convenient to open the hypersonic database manager that gives you access to the data
        in the JBoss hypersonic database.
    </p>

    <p>Start by opening a browser and navigating to the
        jBPM server JMX console. The URL you should use in your browser for
        doing this is : http://localhost:8080/jmx-console. Of course this will
        look slightly different if you are running jBPM on another machine or on
        another port than the default one. A screenshot of the resulting page is
        shown in the figure below.</p>

    <div class="figure"><a name="d0e2421"></a>

        <div class="mediaobject"><img src="images/jmx_console.jpg" alt="The JBoss jBPM JMX Console"></div>
        <p class="title"><b>Figure&nbsp;8.6.&nbsp;The JBoss jBPM JMX Console</b></p></div>
    <p>If you click on the link 'database=localDB,service=Hypersonic'
        under the JBoss entries, you will see the JMX MBean view of the HSQLDB
        database manager. Scrolling a bit down on this page, in the operations
        section, you will see the 'startDatabaseManager()' operation. This is
        illustrated in the screenshot below.</p>

    <div class="figure"><a name="d0e2429"></a>

        <div class="mediaobject"><img src="images/hsqldb_mbean.jpg" alt="The HSQLDB MBean"></div>
        <p class="title"><b>Figure&nbsp;8.7.&nbsp;The HSQLDB MBean</b></p></div>
    <p>Clicking the invoke button will start the HSQLDB Database Manager
        application. This is a rather harsh database client tool, but it works
        ok for our purposes of executing this generated script. You may have to
        ALT-TAB to get to view this application as it may be covered by another
        window. The figure below shows this application with the above script
        loaded and ready to execute. Pushing the 'Execute SQL' button will
        execute the script and effectively update your database.</p>

    <div class="figure"><a name="d0e2437"></a>

        <div class="mediaobject"><img src="images/hsqldb_dbm.jpg" alt="The HSQLDB Database Manager"></div>
        <p class="title"><b>Figure&nbsp;8.8.&nbsp;The HSQLDB Database Manager</b></p></div>
</div>
</div>
<div class="navfooter">
    <hr>
    <table width="100%" summary="Navigation footer">
        <tr>
            <td width="40%" align="left"><a accesskey="p" href="persistence.html">Prev</a>&nbsp;</td>
            <td width="20%" align="center"><a accesskey="u" href="index.html">Up</a></td>
            <td width="40%" align="right">&nbsp;<a accesskey="n" href="processmodelling.html">Next</a></td>
        </tr>
        <tr>
            <td width="40%" align="left" valign="top">Chapter&nbsp;7.&nbsp;持久化&nbsp;</td>
            <td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
            <td width="40%" align="right" valign="top">&nbsp;Chapter&nbsp;9.&nbsp;流程建模</td>
        </tr>
    </table>
</div>
</body>
</html>